﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;
using RedemptoristRetreatCenter.HelperUtility;

namespace RedemptoristRetreatCenter.DatabaseUtility
{
    public class RetreatRegistrationUtility
    {

       private SqlConnection rrcDbConn;

       public RetreatRegistrationUtility()
        {
            rrcDbConn = new SqlConnection();
            rrcDbConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        private void openConnection()
            {
                try
                {
                    rrcDbConn.Open();
                    Console.WriteLine(Environment.NewLine + "rrcDbConn.State: " + rrcDbConn.State.ToString());

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error opening connecdtion: " + ex.ToString());
                }
            }

        public void closeConnection()
        {
            rrcDbConn.Close();
            Console.WriteLine("rrcDbConn.State: " + rrcDbConn.State.ToString());
        }



        public RetreatRegistrationInfo getRetreatAttendanceByAttendanceId(int attendanceId)
        {
            StringBuilder cmdText = new StringBuilder("SELECT att.attendanceId, att.scheduleId, r.title as retreatTitle, att.userId, rtrim(ua.firstName) + ' ' + rtrim(ua.lastName) as attendeeName, att.groupName, ");
            cmdText.Append("att.amountPaid, att.discountAmount, roomType, att.roomAssignment, att.firstFloorRequired, att.dietaryNeeds, att.comment ");
            cmdText.Append("FROM Attendance att ");
            cmdText.Append("inner join RetreatSchedule sch on att.scheduleId=sch.scheduleId ");
            cmdText.Append("inner join Retreat r on sch.retreatId=r.retreatId ");
            cmdText.Append("inner join UserAccount ua on att.userId=ua.userId ");
            cmdText.Append(String.Format("WHERE att.attendanceId={0} and att.activeData=1;", attendanceId));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            List<RetreatRegistrationInfo> retreatAttendance = retrieveRetreatAttendance(sqlCmd);

            if (retreatAttendance != null && retreatAttendance.Count > 0)
            {
                return retreatAttendance[0];  // since scheduleId is unique, only one row will be returned
            }
            else
            {
                return null;
            }
        }

        public List<RetreatRegistrationInfo> getRetreatAttendanceByScheduleId(int scheduleId)
        {
            StringBuilder cmdText = new StringBuilder("SELECT att.attendanceId, att.scheduleId, r.title as retreatTitle, att.userId, rtrim(ua.firstName) + ' ' + rtrim(ua.lastName) as attendeeName, att.groupName, ");
            cmdText.Append("att.amountPaid, att.discountAmount, roomType, att.roomAssignment, att.firstFloorRequired, att.dietaryNeeds, att.comment ");
            cmdText.Append("FROM Attendance att ");
            cmdText.Append("inner join RetreatSchedule sch on att.scheduleId=sch.scheduleId ");
            cmdText.Append("inner join Retreat r on sch.retreatId=r.retreatId ");
            cmdText.Append("inner join UserAccount ua on att.userId=ua.userId ");
            cmdText.Append(String.Format("WHERE att.scheduleId={0} and att.activeData=1;", scheduleId));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            return retrieveRetreatAttendance(sqlCmd);

        }

        public List<RetreatRegistrationInfo> getAllRetreatAttendance()
        {
            StringBuilder cmdText = new StringBuilder("SELECT att.attendanceId, att.scheduleId, r.title as retreatTitle, att.userId, rtrim(ua.firstName) + ' ' + rtrim(ua.lastName) as attendeeName, att.groupName, ");
            cmdText.Append("att.amountPaid, att.discountAmount, roomType, att.roomAssignment, att.firstFloorRequired, att.dietaryNeeds, att.comment ");
            cmdText.Append("FROM Attendance att ");
            cmdText.Append("inner join RetreatSchedule sch on att.scheduleId=sch.scheduleId ");
            cmdText.Append("inner join Retreat r on sch.retreatId=r.retreatId ");
            cmdText.Append("inner join UserAccount ua on att.userId=ua.userId ");
            cmdText.Append(String.Format("WHERE att.activeData=1;"));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            return retrieveRetreatAttendance(sqlCmd);
        }


        private List<RetreatRegistrationInfo> retrieveRetreatAttendance(SqlCommand sqlCmd)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            List<RetreatRegistrationInfo> attendance = new List<RetreatRegistrationInfo>();

            try
            {
                SqlDataReader retreatRegistrationReader = null;
                retreatRegistrationReader = sqlCmd.ExecuteReader();
                RetreatRegistrationInfo retreatRegistrationInfo = null;

                while (retreatRegistrationReader.Read())
                {
                    retreatRegistrationInfo = new RetreatRegistrationInfo();

                    retreatRegistrationInfo.attendanceId = (int)retreatRegistrationReader["attendanceId"];
                    retreatRegistrationInfo.scheduleId = (int)retreatRegistrationReader["scheduleId"];
                    retreatRegistrationInfo.retreatTitle = retreatRegistrationReader["retreatTitle"].ToString();
                    retreatRegistrationInfo.userId = (int)retreatRegistrationReader["userId"];
                    retreatRegistrationInfo.attendeeName = retreatRegistrationReader["attendeeName"].ToString();
                    retreatRegistrationInfo.groupName = retreatRegistrationReader["groupName"].ToString();
                    retreatRegistrationInfo.amountPaid = (double)retreatRegistrationReader["amountPaid"];
                    retreatRegistrationInfo.discountAmount = (double)retreatRegistrationReader["discountAmount"];
                    retreatRegistrationInfo.roomType = retreatRegistrationReader["roomType"].ToString();
                    retreatRegistrationInfo.roomTypeDesc = getRoomTypeDesc(retreatRegistrationReader["roomType"].ToString());
                    retreatRegistrationInfo.firstFloorRequired = (retreatRegistrationReader["firstFloorRequired"].ToString().Equals("1"));
                    retreatRegistrationInfo.dietaryNeeds = retreatRegistrationReader["dietaryNeeds"].ToString();
                    retreatRegistrationInfo.comment = retreatRegistrationReader["comment"].ToString();
                    retreatRegistrationInfo.roomAssignment = retreatRegistrationReader["roomAssignment"].ToString();

                    attendance.Add(retreatRegistrationInfo);

                }
            }
            catch (Exception ex)
            {
                // TODO: 
            }
            finally
            {
                closeConnection();
            }
            return attendance;
        }

        private string getRoomTypeDesc(string roomTypeAbbr)
        {
            if (roomTypeAbbr.Equals("NP"))
            {
                return "No Preference";
            }

            if (roomTypeAbbr.Equals("1B"))
            {
                return "One Bed";
            }

            if (roomTypeAbbr.Equals("2B"))
            {
                return "Two Beds";
            }

            return String.Empty;
        }


        /// <summary>
        /// check if user has already scheduled for a retreat
        /// </summary>
        /// <param name="username"></param>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        public bool isUserRegisteredForRetreat(string username, int scheduleId)
        {
            StringBuilder sqlText = new StringBuilder();
            sqlText.Append("SELECT COUNT(1) AS userRegistered FROM Attendance a inner join UserAccount ua on a.userId=ua.userId ");
            sqlText.Append("WHERE ua.username='");
            sqlText.Append(username);
            sqlText.Append("' and a.scheduleId=");
            sqlText.Append(scheduleId);
            sqlText.Append(" and a.activeData=1;");
            SqlCommand sqlCmd = new SqlCommand(String.Format(sqlText.ToString(), rrcDbConn));
            
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowCount = 0;  

            try 
            {
                SqlDataReader retreatRegistrationReader = null;
                retreatRegistrationReader = sqlCmd.ExecuteReader();

                if (retreatRegistrationReader.Read())
                {
                    rowCount = (int)retreatRegistrationReader["userRegistered"];
                }
            }
            catch (Exception ex)
            {
                // TODO: 
            }
            finally 
            {
                closeConnection();
            }

            return (rowCount > 0);
        }

        /// <summary>
        /// Register user for specified retreat
        /// </summary>
        /// <param name="username"></param>
        /// <param name="scheduleId"></param>
        /// <returns>True if successful registration or user is already registered</returns>
        public bool registerForRetreat(RetreatRegistrationInfo regInfo, string username)
        {
            if (this.isUserRegisteredForRetreat(username, regInfo.scheduleId))
            {
                return true;    // if user already registered then there is nothing to do
            }

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            ConversionUtility converter = new ConversionUtility();

            try
            {
                StringBuilder sqlText = new StringBuilder("INSERT INTO Attendance (scheduleId, userId, groupName, amountPaid, discountAmount, roomType, ");
                sqlText.Append("firstFloorRequired, dietaryNeeds, comment, roomAssignment, modifiedBy, modifiedTimestamp, activeData) Values (");
                sqlText.Append(String.Format("{0},{1},'{2}',{3},{4},'{5}',", regInfo.scheduleId, regInfo.userId, regInfo.groupName, regInfo.amountPaid, regInfo.discountAmount, regInfo.roomType));
                sqlText.Append(String.Format("{0}, '{1}','{2}','{3}','{4}','{5}',1);",converter.booleanToInt(regInfo.firstFloorRequired), regInfo.dietaryNeeds, regInfo.comment, regInfo.roomAssignment, username, DateTime.Now.ToString()));
                SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                // TODO:
                Console.WriteLine("Failed to insert registration.  Exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);

        }

        public bool updateRetreatRegistration(RetreatRegistrationInfo regInfo, string username)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            ConversionUtility converter = new ConversionUtility();
            try
            {
                StringBuilder cmdText = new StringBuilder(String.Format("UPDATE Attendance SET (scheduleId='{0}',groupName='{1}',amountPaid={2},discountAmount={3},roomType='{4}', ", regInfo.scheduleId, regInfo.groupName, regInfo.amountPaid, regInfo.discountAmount, regInfo.roomType));
                cmdText.Append(String.Format("firstFloorRequired={0}, dietaryNeeds='{1}', comment='{2}', roomAssignment='{3}', modifiedBy='{4}',modifiedTimestamp='{5}')", converter.booleanToInt(regInfo.firstFloorRequired), regInfo.dietaryNeeds, regInfo.comment, regInfo.roomAssignment, username, DateTime.Now.ToString()));
                cmdText.Append(String.Format("WHERE attendanceId={0};",regInfo.attendanceId));    
                // update of userId is intentionally excluded.  Once a user has been registered for a retreat, the user cannot be changed. The registration can be deleted if userId is incorrect.

                SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update Attendance.  Exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

        public bool deleteRetreatSchedule(int attendanceId, string username)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string cmdText = String.Format("UPDATE Attendance SET activeData=0, modifiedBy='{0}', modifiedTimestamp='{1}' WHERE attendanceId={2};", username, DateTime.Now, attendanceId);
                SqlCommand sqlCmd = new SqlCommand(cmdText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }
        
    }
}